import pymysql


# 连接数据库
def connect():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
        port=3306,
        database='test'
    )
    return conn


# 查询
def query():
    # 建立连接
    conn = connect()

    # 创建游标，查询数据以元组形式返回
    # cursor = conn.cursor()
    # 创建游标，查询数据以字典形式返回
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    sql = 'select * from gwos_field_config limit 10'
    try:
        cursor.execute(sql)
        # result = cursor.fetchall()  # 返回所有数据
        # result = cursor.fetchone()  # 返回一行数据
        result = cursor.fetchmany(2)  # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
        print(result)
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()


# 插入
def insert():
    # 建立连接
    conn = connect()
    # 创建游标，查询数据以字典形式返回
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # 元祖、列表形式传参
    sql = "insert into gwos_work_order_billno(work_order_no,bill_no,work_order_type,trouble_type_code) values (%s,%s,%s,%s)"
    try:
        cursor.execute(sql, ("abc", "efg", "test", "hig"))
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()

    # 字典形式传参
    sql = "insert into gwos_work_order_billno(work_order_no,bill_no,work_order_type,trouble_type_code) values (%(work_order_no)s,%(bill_no)s,%(work_order_type)s,%(trouble_type_code)s)"
    try:
        cursor.execute(sql,
                       {"work_order_no": "111", "bill_no": "222", "work_order_type": "333", "trouble_type_code": "444"})
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()


# 批量插入
def insertBatch():
    # 建立连接
    conn = connect()
    # 创建游标，查询数据以字典形式返回
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%s, %s, %s)'
    # 元组列表作为传入参数
    param = [("999999", "kenny", 28), ("999998", "liu", 28)]
    try:
        cursor.executemany(sql, param)
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()


# 更新
def update():
    # 建立连接
    conn = connect()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'update gwos_work_order_billno set work_order_no=%s where id = %s'
    try:
        cursor.execute(sql, ['liu', 111111])
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()


# 删除
def delete():
    # 建立连接
    conn = connect()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'delete from  player_info_test where PERSON_ID = %s'
    try:
        cursor.execute(sql, ['999999'])
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()


if __name__ == '__main__':
    query()
